package cn.frontop.funswitch.util;

import cn.frontop.funswitch.entity.DatasourceEntity;
import cn.frontop.funswitch.exception.FunSwitchException;
import cn.hutool.cache.Cache;
import cn.hutool.cache.CacheUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.handler.EntityListHandler;
import cn.hutool.db.sql.SqlExecutor;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Yun
 */
@Slf4j
public class SqlUtil {

    /**
     * 敏感sql操作
     */
    public static final String REG_SENSITIVE_SQL = "drop\\s|alter\\s|grant\\s|insert\\s|replace\\s|delete\\s|truncate\\s|update\\s|remove\\s";
    public static final Pattern PATTERN_SENSITIVE_SQL = Pattern.compile(REG_SENSITIVE_SQL);

    private static final Cache<Integer, Connection> CACHE = CacheUtil.newFIFOCache(1);
    private static Pattern LINE_PATTERN = Pattern.compile("_(\\w)");


    /**
     * 检查敏感操作
     *
     * @param sql
     * @throws RuntimeException
     */
    public static void checkSensitiveSql(String sql) throws RuntimeException {
        Matcher matcher = PATTERN_SENSITIVE_SQL.matcher(sql.toLowerCase());
        if (matcher.find()) {
            String group = matcher.group();
            log.warn("不允许执行查询以外的操作！！！ SQL:{}", sql);
            throw new FunSwitchException("违规SQL:{} " + group.toUpperCase());
        }
    }

    /**
     * 执行SQL
     *
     * @param datasourceEntity 数据源配置
     * @param sql              执行sql
     * @return 执行结果
     */
    public static Object execute(DatasourceEntity datasourceEntity, String sql) {
        //检查SQL
        checkSensitiveSql(sql);
        CommonUtil.isEmpty(datasourceEntity, "数据库对象为空!");
        Object result = null;
        Connection connection = null;
        try {
            connection = createConnection(datasourceEntity);
            result = SqlExecutor.query(connection, sql, new EntityListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    /**
     * 创建连接
     *
     * @param datasourceEntity
     * @return
     * @throws SQLException
     */
    private static Connection createConnection(DatasourceEntity datasourceEntity) throws SQLException {
        if (CACHE.get(datasourceEntity.getId()) == null) {
            DruidDataSource ds2 = new DruidDataSource();
            ds2.setUrl(datasourceEntity.getUrl());
            ds2.setUsername(datasourceEntity.getUserName());
            ds2.setPassword(datasourceEntity.getPassWord());
            CACHE.put(datasourceEntity.getId(), ds2.getConnection());
        } else if (CACHE.get(datasourceEntity.getId()).isClosed() || !CACHE.get(datasourceEntity.getId()).isValid(3000)) {
            DruidDataSource ds2 = new DruidDataSource();
            ds2.setUrl(datasourceEntity.getUrl());
            ds2.setUsername(datasourceEntity.getUserName());
            ds2.setPassword(datasourceEntity.getPassWord());
            CACHE.put(datasourceEntity.getId(), ds2.getConnection());
        }
        return CACHE.get(datasourceEntity.getId());
    }

    /**
     * 转换 驼峰命名
     *
     * @param examples 需要转换对象
     * @param change   是否转换
     * @return 转换结果
     */
    public static ArrayList<HashMap> changeJava(List<Entity> examples, boolean change) {
        ArrayList<Entity> arrayList = (ArrayList<Entity>) examples;
        ArrayList<HashMap> list = new ArrayList<HashMap>();
        for (Entity entity : arrayList) {
            HashMap<String, Object> hashMap = new HashMap<>();
            for (String key : entity.keySet()) {
                if (change) {
                    hashMap.put(lineToHump(key), entity.get(key));
                } else {
                    hashMap.put(key, entity.get(key));
                }
            }
            list.add(hashMap);
        }
        return list;
    }

    /**
     * 驼峰命名
     *
     * @param str 需要转换
     * @return 转换结果
     */
    public static String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = LINE_PATTERN.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }
}
